#!/usr/bin/python 
# @Time  : 2022/6/2 19:05
# @Author: LOUIE
# @Desc  : 数据库操作类
import pymysql
from dbutils.pooled_db import PooledDB
from timeit import default_timer
from utils.logger import log


_host = "8.142.25.43"
_port = 3306
_user = "root"
_password = "mashibing123"
_database = "mall_trade"


class DB_Config:

    def __init__(self, host, port, user, password, database=None):
        self.host = host
        self.port = port
        self.user = user
        self.password = password
        self.database = database

        self.MAX_CONNECTIONS = 100  # 创建连接池的最大数量
        self.MIN_CACHED = 10   # 连接池中空闲连接的初始数量
        self.MAX_CACHED = 20   # 连接池中空闲链接的最大数量
        self.MAX_SHARED = 10   # 共享链接的最大数量
        self.BLOCK = True      # 超时最大链接数量时的表现，为True等待连接数量下降，为false直接报错处理
        self.MAX_USAGE = 100   # 单个连接的最大重复使用次数
        self.CHARSET = 'UTF8'
        self.RESET = True
        self.SET_SESSION = ['SET AUTOCOMMIT = 1']  # 设置自动提交


class DB_MySQL_Pool:

    __pool = None

    def __init__(self, config):
        if not self.__pool:
            try:
                self.__class__.__pool = PooledDB(
                    creator=pymysql, host=config.host, port=config.port,
                    user=config.user, password=config.password, database=config.database,
                    maxconnections=config.MAX_CONNECTIONS, mincached=config.MIN_CACHED,
                    maxcached=config.MAX_CACHED, maxshared=config.MAX_SHARED,
                    maxusage=config.MAX_USAGE, reset=config.RESET,
                    setsession=config.SET_SESSION, charset=config.CHARSET
                )
                log.info("数据库连接成功, 地址：{}".format(config.host))
            except ConnectionError as ex:
                log.error("数据库连接时发生错误, Error: {}".format(ex), exc_info=True)
                raise ex

    def get_connection(self):
        return self.__pool.connection()




class DB_MySQL:

    def __init__(self) -> None:
        config = DB_Config(_host, _port, _user, _password, _database)
        self._log_time = True
        self.connects_pool = DB_MySQL_Pool(config)

    def __enter__(self):
        if self._log_time is True:
            self._start = default_timer()
        connect = self.connects_pool.get_connection()
        cursor = connect.cursor(pymysql.cursors.DictCursor)
        self._connect = connect
        self._cursor = cursor
        return self

    def __exit__(self, *exc_info):
        self._connect.commit()
        self._cursor.close()
        self._connect.close()

        if self._log_time is True:
            diff_time = round(default_timer() - self._start, 2)
            log.info(f'SQL cost time: {diff_time} s')

    def fetch_all(self, sql):
        self._cursor.execute(sql)
        log.info('execute SQL：{}'.format(sql))
        return self._cursor.fetchall()

    def fetch_one(self, sql):
        self._cursor.execute(sql)
        log.info('execute SQL：{}'.format(sql))
        return self._cursor.fetchone()


if __name__ == '__main__':
    with DB_MySQL() as db:
        print(db.fetch_all("SELECT * FROM mall_trade.`trade_order` "
                            "WHERE DATE_FORMAT(submit_time, '%Y %m') "
                            "= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 0 MONTH),'%Y %m') "
                            "AND is_deleted = 0;"))
    with DB_MySQL() as db:
        print(db.fetch_all("SELECT * FROM mall_trade.`trade_order` "
                            "WHERE DATE_FORMAT(submit_time, '%Y %m') "
                            "= DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 0 MONTH),'%Y %m') "
                            "AND is_deleted = 0;"))